package org.sql.statement.builder;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.sql.statement.builder.WhereClause.LogicConnector;
import org.sql.statement.builder.WhereClause.Op;
import org.sql.statement.operation.DeleteOperation;
import org.sql.statement.operation.InsertOperation;
import org.sql.statement.operation.SelectOperation;
import org.sql.statement.operation.UpdateOperation;
import org.sql.statement.operation.intf.Operation;
import org.sql.statement.util.StatementUtil;

public class StatementBuilder extends AbstractStatementBuilder {

    enum Order {
        ASC, DESC;
    }

    public enum StatementType {
        DELETE, INSERT, SELECT, UPDATE;
    }

    private String builtQuery = null;

    private List<String> columns = null;

    private String comment;

    private List<String> groupBy = null;

    private Map<String, Object> insertArguments = null;

    private boolean isForUpdate = false;

    private boolean isNoWait = false;

    private boolean isReadOnly = false;

    private boolean isSelectAsterisk = false;

    private Map<String, Order> orderBy = null;

    private List<String> tables = null;

    private StatementType type = null;

    private List<WhereClause> whereArguments = null;

    public StatementBuilder() {
        this.insertArguments = new HashMap<String, Object>();
        this.whereArguments = new ArrayList<WhereClause>();
        this.groupBy = new ArrayList<String>();
        this.orderBy = new HashMap<String, Order>();
        this.tables = new ArrayList<String>();
        this.columns = new ArrayList<String>();
        this.isSelectAsterisk = false;
        this.type = StatementType.SELECT;
        this.isReadOnly = false;
        this.builtQuery = "";
        this.isForUpdate = false;
        this.isNoWait = false;
    }

    private StatementBuilder(StatementBuilder statementBuilder) {
        this.insertArguments = new HashMap<String, Object>(statementBuilder.insertArguments);
        this.whereArguments = new ArrayList<WhereClause>(statementBuilder.whereArguments);
        this.groupBy = new ArrayList<String>(statementBuilder.groupBy);
        this.orderBy = new HashMap<String, Order>(statementBuilder.orderBy);
        this.tables = new ArrayList<String>(statementBuilder.tables);
        this.columns = new ArrayList<String>(statementBuilder.columns);

        this.type = statementBuilder.type;
        this.isSelectAsterisk = statementBuilder.isSelectAsterisk;
        this.isReadOnly = statementBuilder.isReadOnly;
        this.builtQuery = statementBuilder.builtQuery;
    }

    public StatementBuilder all() {
        if (!this.isReadOnly)
            this.isSelectAsterisk = true;

        return cloneCurrentBuilder();
    }

    public StatementBuilder and(String field, Op operator, Object... values) {
        return this.where(field, operator, LogicConnector.AND, values);
    }

    public StatementBuilder asc(String field) {
        verifyIfImmutable();

        this.orderBy.put(field, Order.ASC);

        return cloneCurrentBuilder();
    }

    public StatementBuilder between(String field, Object first, Object second) {
        return where(field, Op.BETWEEN, first, second);
    }

    public String build() {
        if (!this.isReadOnly) {
            // this.isReadOnly = true;
            this.builtQuery = buildStatement();
        }

        return this.builtQuery;
    }

    public PreparedStatement build(Connection conn) throws SQLException {
        if (conn == null)
            return null;

        final List<String> values = new ArrayList<String>();

        for (Object arg : this.insertArguments.values())
            values.add(arg.toString());

        for (WhereClause clause : this.whereArguments)
            for (Object arg : Arrays.asList(clause.getValues()))
                values.add(arg.toString());

        return conn.prepareStatement(this.build(), values.toArray(new String[] {}));
    }

    private Object buildGroupByFragment(List<String> groupBy) {
        final StringBuilder sb = new StringBuilder("GROUP BY").append(CHAR_SPACE);

        for (String string : groupBy)
            sb.append(string).append(STR_COMMA_SPACE);

        return StatementUtil.getAllButLast(sb, STR_COMMA_SPACE.length());
    }

    private String buildOrderByFragment(Map<String, Order> orderBy) {
        final StringBuilder sb = new StringBuilder("ORDER BY").append(CHAR_SPACE);

        final Object[] array = orderBy.keySet().toArray();
        for (int i = array.length - 1; i > -1; i--)
            sb.append(array[i].toString()).append(CHAR_SPACE).append(orderBy.get(array[i].toString()).toString()).append(STR_COMMA_SPACE);

        return StatementUtil.getAllButLast(sb, STR_COMMA_SPACE.length());
    }

    private String buildStatement() {
        Operation statement = null;

        switch (this.type) {
        case SELECT:
            statement = new SelectOperation();
            break;
        case INSERT:
            statement = new InsertOperation();
            break;
        case UPDATE:
            statement = new UpdateOperation();
            break;
        case DELETE:
            statement = new DeleteOperation();
            break;
        default:
            throw new RuntimeException("Statement Type not set!");
        }

        final StringBuilder sb = new StringBuilder(getComments(this.comment));

        sb.append(statement.buildStatement(this)).append(CHAR_SPACE);

        sb.append(getWhereClause(this.whereArguments));

        sb.append(getOrderByClause(this.orderBy));

        sb.append(getGroupByClause(this.groupBy));

        if (this.isForUpdate) {
            sb.append("FOR UPDATE");

            if (this.isNoWait)
                sb.append(" NOWAIT");
        }

        return sb.toString().trim();
    }

    private StatementBuilder cloneCurrentBuilder() {
        return new StatementBuilder(this);
    }

    public StatementBuilder comment(String comment) {
        this.comment = comment;

        return this;
    }

    public StatementBuilder count() {
        if (!this.isSelectAsterisk)
            select("COUNT(1)");

        return this;
    }

    public StatementBuilder desc(String field) {
        verifyIfImmutable();

        this.orderBy.put(field, Order.DESC);

        return this;
    }

    public StatementBuilder forUpdate() {
        this.isForUpdate = true;

        return this;
    }

    public StatementBuilder from(String... tables) {
        this.tables.addAll(Arrays.asList(tables));

        return this;
    }

    public List<String> getColumns() {
        return this.columns;
    }

    private StringBuffer getComments(String comment) {
        final StringBuffer sb = new StringBuffer();

        if (comment != null && !this.comment.trim().isEmpty())
            sb.append("/*").append(CHAR_SPACE).append(comment).append(CHAR_SPACE).append("*/").append(CHAR_SPACE);

        return sb;
    }

    private Object getGroupByClause(List<String> groupBy) {
        final StringBuilder sb = new StringBuilder();

        if (!groupBy.isEmpty())
            sb.append(buildGroupByFragment(groupBy)).append(Constants.CHAR_SPACE);

        return sb;
    }

    public Map<String, Object> getInsertArguments() {
        return new HashMap<String, Object>(this.insertArguments);
    }

    private Object getOrderByClause(Map<String, Order> orderBy) {
        final StringBuilder sb = new StringBuilder();

        if (!orderBy.isEmpty())
            sb.append(buildOrderByFragment(orderBy)).append(Constants.CHAR_SPACE);

        return sb;
    }

    // public StatementBuilder gt(String field, Object value) {
    // return where(field, Operator.GT, value);
    // }
    //
    // public StatementBuilder in(String field, Object... values) {
    // return where(field, Operator.IN, values);
    // }

    public List<String> getTables() {
        return this.tables;
    }

    public List<WhereClause> getWhereArguments() {
        return new ArrayList<WhereClause>(this.whereArguments);
    }

    // public StatementBuilder isNull(String field, Object value) {
    // return where(field, Operator.IS_NULL, (Object[]) null);
    // }

    private Object getWhereClause(List<WhereClause> whereArguments) {
        final StringBuilder sb = new StringBuilder();

        if (!whereArguments.isEmpty())
            sb.append(buildWhereFragment(whereArguments)).append(CHAR_SPACE);

        return sb;
    }

    // public StatementBuilder lt(String field, Object value) {
    // return where(field, Operator.LT, value);
    // }
    //
    // public StatementBuilder notEq(String field, Object value) {
    // return where(field, Operator.NEQ, value);
    // }
    //
    // public StatementBuilder notIn(String field, Object... values) {
    // return where(field, Operator.NIN, values);
    // }

    // public StatementBuilder notNull(String field, ) {
    // return where(field, Operator.NOT_NULL, (Object[]) null);
    // }

    public StatementBuilder groupBy(String column) {
        verifyIfImmutable();

        this.groupBy.add(column);

        return this;
    }

    public StatementBuilder insert() {
        verifyIfImmutable();

        this.type = StatementType.INSERT;

        return cloneCurrentBuilder();
    }

    public StatementBuilder into(String... tables) {
        verifyIfImmutable();

        this.tables.addAll(Arrays.asList(tables));

        return cloneCurrentBuilder();
    }

    public boolean isSelectAsterisk() {
        return this.isSelectAsterisk;
    }

    public synchronized StatementBuilder noWait() {
        this.isForUpdate = true;
        this.isNoWait = true;

        return this;
    }

    public StatementBuilder or(String field, Op operator, Object... values) {
        return this.where(field, operator, LogicConnector.OR, values);
    }

    public StatementBuilder orderBy(String field) {
        return desc(field);
    }

    public String query() {
        return this.builtQuery;
    }

    public synchronized StatementBuilder select() {
        this.isSelectAsterisk = true;
        this.columns = new ArrayList<String>();

        return this;
    }

    public StatementBuilder select(String... columns) {
        this.columns.addAll(Arrays.asList(columns));

        return this;
    }

    public StatementBuilder set(Map<String, Object> args) {
        verifyIfImmutable();

        this.insertArguments.putAll(new HashMap<String, Object>(args));

        return this;
    }

    public StatementBuilder set(String field, Object value) {
        verifyIfImmutable();

        this.insertArguments.put(field, value);

        return this;
    }

    public StatementBuilder setImmutable() {
        this.isReadOnly = true;

        return this;
    }

    @Override
    public String toString() {
        return this.buildStatement();
    }

    private void verifyIfImmutable() {
        if (this.isReadOnly)
            StatementUtil.throwException("Statement is immutable!", this.builtQuery);
    }

    public StatementBuilder where(String field, Object value) {
        return where(field, Op.EQ, value);
    }

    public StatementBuilder where(String field, Op operation, LogicConnector connector, Object... value) {
        verifyIfImmutable();

        this.whereArguments.add(new WhereClause(field, operation, connector, value));

        return this;
    }

    public StatementBuilder where(String field, Op operation, Object... value) {
        verifyIfImmutable();

        this.whereArguments.add(new WhereClause(field, operation, null, value));

        return this;
    }
}
